package com.zyh.demo;

import com.zyh.domain.Student;
import com.zyh.utils.JDBCUtil;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author zyh
 * @Date 2024/8/22 11:18
 * @Description
 */

public class JDBC_Demo1 {
    public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/jdbc_db1?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    // 增加数据
    @Test
    public void test1() throws Exception {
        //1. 加载MySQL驱动
        Class.forName(MYSQL_DRIVER);
        //2. 创建连接对象
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        //3. 获取发送执行对象 statement
        Statement statement = connection.createStatement();
        //4. 创建sql语句
        String sql = "insert into student value (null,\"cxk\",22,\"女\",\"湖北武汉\")";
        //5. 通过 statement 对象执行sql语句
        int count = statement.executeUpdate(sql);
        //6. 拿到返回结果
        System.out.println(count == 1 ? "添加成功！" : "添加失败！");
        //7. 关闭资源
        statement.close();
        connection.close();
    }

    // 修改数据 id=1 姓名为 zyh
    @Test
    public void test2() {
        Connection connection = null;
        Statement statement = null;
        try {
            //1. 加载 MySQL 驱动
            Class.forName(MYSQL_DRIVER);
            //2. 创建 MySQL 连接
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            //3. 创建发送执行的对象 statement
            statement = connection.createStatement();
            //4. 定义sql语句
            String sql = "update student set name='zyh' where id=1";
            //5. 使用 statement 对象执行sql语句
            int count = statement.executeUpdate(sql);
            //6. 处理返回结果
            System.out.println(count == 1 ? "修改成功" : "修改失败");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7. 关闭资源
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 删除 id=1 的记录
    @Test
    public void test3() throws Exception {
        //1. 加载 MySQL 驱动
        Class.forName(MYSQL_DRIVER);
        //2. 创建 MySQL 连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        //3. 创建发送，执行的 Statement 对象
        Statement statement = connection.createStatement();
        //4. 定义 sql 语句
        String sql="delete from student where id=1";
        //
        int count = statement.executeUpdate(sql);
        System.out.println(count == 1 ? "删除成功" : "删除失败");
        statement.close();
        connection.close();
    }

    // 查询所有数据
    @Test
    public void test4() throws Exception {
        // 1. 加载 MySQL　驱动
        Class.forName(MYSQL_DRIVER);
        // 2. 创建连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        // 3. 创建发送，执行对象 Statement
        Statement statement = connection.createStatement();
        // 4. 定义 sql 语句
        String sql = "select * from student";
        // 5. 执行 sql 语句
        ResultSet resultSet = statement.executeQuery(sql);
        List<Student> list = new ArrayList<>();
        // 6. 处理返回结果
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String sex = resultSet.getString("sex");
            String address = resultSet.getString("address");
            Student student=new Student(id,name,age,sex,address);
            list.add(student);
        }
        // 遍历集合
        list.forEach(System.out::println);

        // 关闭资源
        JDBCUtil.close(resultSet,statement,connection);
    }
}
